﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Data;
using System.Data.SQLite;
using System.Data;

namespace DisplayBoardInfo.DataSource
{
    public partial class DataSource
    {


        public SQLiteConnection con { get; private set; }

        public string Login { get; set; }

        public DataSourceObject FetchData()
        {
            return GetPhysicianVisits(this.Login);
        }

        private void InitConnection()
        {
            con = new SQLiteConnection("Data Source=siwoz.db;Version=3;New=False;Compress=True;");
            con.Open();
        }

        public DataSourceObject GetPhysicianVisits(string login)
        {
            InitConnection();
            var result = new DataSourceObject() { Visits = new List<PatientVisit>() };

            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT p.*, a.*, pa.id, pa.birth_year, pa.name as pname FROM physicians p join appointments a on a.physician_id = p.id" +
                " join Patients pa on a.patient_id = pa.id";

            if (login != null)
            {
                cmd.CommandText += " where login = @Login";
                var loginParam = new SQLiteParameter() { ParameterName = "@Login", Value = login };
                cmd.Parameters.Add(loginParam);
            }

            SQLiteDataReader reader = cmd.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();

            if (dt.Rows == null || dt.Rows.Count == 0)
            {
                return result;
            }
            else
            {
                var firstRow = dt.Rows[0];
                var physician = new Physician() { Login = firstRow["login"].ToString(), Name = firstRow["name"].ToString(), Speciality = firstRow["speciality"].ToString() };
                result.Physician = physician;

                foreach (DataRow row in dt.Rows)
                {
                    var visit = new PatientVisit()
                    {
                        BirthDate = row["birth_year"].ToString(),
                        Name = row["pname"].ToString(),
                        VisitBegin = row["visit_start"].ToString().Substring(10, 6),
                        VisitEnd = row["visit_end"].ToString().Substring(10, 6)
                    };
                    result.Visits.Add(visit);
                }
            }
            return result;
        }

    }
}
